package com.yuyue.springFramework.note15;

import com.alibaba.druid.pool.DruidDataSource;
import com.yuyue.springFramework.note15.entity.Person;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 《Spring-15 JdbcTemplate》文章代码
 *  文章地址：https://mp.weixin.qq.com/s/If8hgnP3uB2WmB1Fjr3bpA
 */
public class Note15Test {

    @Test
    //简单使用，Spring的数据源
    public void fun1(){
        //获取数据源
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        //驱动
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        //数据库连接
        dataSource.setUrl("jdbc:mysql://localhost:3306/my_schema?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai");
        //用户
        dataSource.setUsername("root");
        //密码
        dataSource.setPassword("root");

        //获取 JdbcTemplate
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        //批量查询
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from person where id = 1");
        System.out.println(list);
    }

    @Test
    //简单使用，德鲁伊连接池
    public void fun2(){
        //获取数据源
        DruidDataSource dataSource = new DruidDataSource();
        //驱动
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        //数据库连接
        dataSource.setUrl("jdbc:mysql://localhost:3306/my_schema?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai");
        //用户
        dataSource.setUsername("root");
        //密码
        dataSource.setPassword("root");

        //获取 JdbcTemplate
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        //批量查询
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from person where id = 1");
        System.out.println(list);
    }

    @Test
    //增删改查，无参
    public void fun3(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("ApplicationContext07.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);

        //插入
        int i = jdbcTemplate.update("insert into person(name, age, sex, id) values ('鱼跃', 18, '男', 9)");
        System.out.println("插入 " + i + " 条记录");

        //查询
        String name = jdbcTemplate.queryForObject("select name from person where id = 9", String.class);
        System.out.println(name);

        //修改
        i = jdbcTemplate.update("update person set name = '飞鸟'  where id = 9");
        System.out.println("修改 " + i + " 条记录");

        //查询
        name = jdbcTemplate.queryForObject("select name from person where id = 9", String.class);
        System.out.println(name);

        //手动报错
        //System.out.println(1/0);

        //删除
        i = jdbcTemplate.update("delete from person where id = 9");
        System.out.println("删除 " + i + " 条记录");

//        //查询不到记录，报错
//        name = jdbcTemplate.queryForObject("select name from person where id = 9", String.class);
//        System.out.println(name);
    }

    @Test
    //增删改查，有参
    public void fun4(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("ApplicationContext07.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);

        //插入
        int i = jdbcTemplate.update("insert into person(name, age, sex, id) values (?, ?, ?, ?)",new Object[]{"鱼跃", 18, "男", 9});
        System.out.println("插入 " + i + " 条记录");

        //查询
        String name = jdbcTemplate.queryForObject("select name from person where id = ?", String.class, 9);
        System.out.println(name);

        //修改
        i = jdbcTemplate.update("update person set name = '飞鸟'  where id = ?", 9);
        System.out.println("修改 " + i + " 条记录");

        //查询
        name = jdbcTemplate.queryForObject("select name from person where id = ?", String.class, 9);
        System.out.println(name);

        //删除
        i = jdbcTemplate.update("delete from person where id = ?", 9);
        System.out.println("删除 " + i + " 条记录");
    }

    @Test
    //增删改查，有参2
    public void fun5(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("ApplicationContext07.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);

        //插入。以PreparedStatementSetter 的形式为sql提供参数。
        int i = jdbcTemplate.update("insert into person(name, age, sex, id) values (?, ?, ?, ?)", new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, "鱼跃");
                ps.setInt(2, 18);
                ps.setString(3, "男");
                ps.setString(4, "10");
            }
        });
        System.out.println("插入 " + i + " 条记录");

        //查询
        String name = jdbcTemplate.queryForObject("select name from person where id = ?", String.class, 10);
        System.out.println(name);

    }

    @Test
    //编码实现对象返回
    public void fun6(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("ApplicationContext07.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);

        //查询
        Person person = jdbcTemplate.queryForObject("select * from person where id = ?",  new RowMapper<Person>() {
            //创建行映射器接口对象，并重写行映射方法
            @Override
            public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
                Person person = new Person();
                //按查询结果顺序映射
                person.setName(rs.getString(1));
                person.setAge(rs.getInt(2));
                person.setSex(rs.getString(3));
                person.setId(rs.getString(4));

                return person;
            }
        }, 10);
        System.out.println(person);
    }

    @Test
    //Spring实现对象返回
    public void fun7(){
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("ApplicationContext07.xml");
        JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);

        // 定义行映射器
        RowMapper<Person> rowMapper = new BeanPropertyRowMapper<>(Person.class);

        //查询
        Person person = jdbcTemplate.queryForObject("select * from person where id = ?", rowMapper, 10);
        System.out.println(person);
    }
}
